Teiid supports creating temporary,or "temp", tables. Temp tables are dynamically created, but are treated as any other physical table.

Temp tables can be defined implicitly by referencing them in a INSERT statement or explicitly with a CREATE TABLE statement. Implicitly created temp tables must have a name that starts with '#'.

Creation syntax:

Explicit:

CREATE LOCAL TEMPORARY TABLE x \(column type \[NOT NULL], ... \[PRIMARY KEY \(column, ...)])

Implicit:

INSERT INTO #x \(column, ...) VALUES \(value, ...) 

If #x doesn't exist, it will be defined using the given column names and types from the value expressions.

Implicit:

INSERT INTO #x \[(column, ...)] select c1, c2 from t

If #x doesn't exist, it will be defined using the target column names (in not supplied, the column names will match the derived column names from the query), and the types from the query derived columns.

• Use the SERIAL data type to specify a NOT NULL and auto-incrementing INTEGER column. The starting value of a SERIAL column is 1.

Drop syntax:

• DROP TABLE x

Primary Key Support

• All key columns must be comparable.

• Use of a primary key creates a clustered index that supports search improvements for comparison, in, like, and order by.

• Null is an allowable primary key value, but there must be only 1 row that has an all null key.
  Limitations:

• With the CREATE TABLE syntax only basic table definition (column name and type information) and an optional primary key are supported.

• The "ON COMMIT" clause is not supported in the CREATE TABLE statement.

• "drop behavior" option is not supported in the drop statement.

• Only local temporary tables are supported. This implies that the scope of temp table will be either to the session or the block of a virtual procedure that creates it.

• Session level temp tables are not fail-over safe.

• Temp tables support a READ_UNCOMMITED transaction isolation level. There are no locking mechanisms available to support higher isolation levels and the result of a rollback may be inconsistent across multiple transactions. If concurrent transactions are not associated with the same local temporary table or session, then the transaction isolation level is effectively SERIALIZABLE. If you want full consistency with local temporary tables, then only use a connection with 1 transaction at a time. This mode of operation is ensured by connection pooling that tracking connections by transaction.

• Lob values (xml, clob, blob) are tracked by reference rather than by value in a temporary table. Lob values from external sources that are inserted in a temporary table may become unreadable when the associated statement or connection is closed.

The following example is a series of statements that loads a temporary table with data from 2 sources, and with a manually inserted record, and then uses that temp table in a subsequent query.

... 
CREATE LOCAL TEMPORARY TABLE TEMP (a integer, b integer, c integer); 
SELECT * INTO temp FROM Src1; SELECT * INTO temp FROM Src2; 
INSERT INTO temp VALUES (1,2,3); 
SELECT a,b,c FROM Src3, temp WHERE Src3.a = temp.b; 
...

See Virtual Procedures for more on temp table usage.